package cn.ctbu.xiaopeng.studentscoremanage.dao;

import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.TcQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.TC;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.TcPageVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author xiaopeng
 * @version 1.0
 */
public interface TCRepository extends JpaRepository<TC, Integer> {

    /**
     * 根据条件查询教师课程分页信息
     * @param tcQueryDTO
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.TcPageVO(" +
            "tc.id,t.tno, c.courseNo, c.cname, t.name, t.sex, t.age, c.credits, c.hours, " +
            "c.semester, c.assessMethod, dep.department, t.qualifications) " +
            "FROM tb_tc tc " +
            "JOIN tb_teacher t ON tc.tno = t.tno " +  // 连接教师表
            "JOIN tb_course c ON tc.courseNo = c.courseNo " +  // 连接课程表
            "JOIN tb_department dep ON t.departmentId = dep.departmentId " +  // 连接部门表
            "WHERE (:#{#tcQueryDTO.tno} IS NULL OR t.tno LIKE %:#{#tcQueryDTO.tno}%) " +
            "AND (:#{#tcQueryDTO.cname} IS NULL OR c.cname LIKE %:#{#tcQueryDTO.cname}%) " +
            "AND (:#{#tcQueryDTO.tname} IS NULL OR t.name LIKE %:#{#tcQueryDTO.tname}%) " +
            "AND (:#{#tcQueryDTO.sex} IS NULL OR t.sex = :#{#tcQueryDTO.sex}) " +
            "AND (:#{#tcQueryDTO.age} IS NULL OR t.age = :#{#tcQueryDTO.age}) " +
            "AND (:#{#tcQueryDTO.credits} IS NULL OR c.credits = :#{#tcQueryDTO.credits}) " +
            "AND (:#{#tcQueryDTO.hours} IS NULL OR c.hours = :#{#tcQueryDTO.hours}) " +
            "AND (:#{#tcQueryDTO.semester} IS NULL OR c.semester LIKE %:#{#tcQueryDTO.semester}%) " +
            "AND (:#{#tcQueryDTO.assessMethod} IS NULL OR c.assessMethod LIKE %:#{#tcQueryDTO.assessMethod}%) " +
            "AND (:#{#tcQueryDTO.department} IS NULL OR dep.department LIKE %:#{#tcQueryDTO.department}%) " +
            "AND (:#{#tcQueryDTO.qualifications} IS NULL OR t.qualifications LIKE %:#{#tcQueryDTO.qualifications}%)")
    Page<TcPageVO> getTcPageByConditions(@Param("tcQueryDTO") TcQueryDTO tcQueryDTO, Pageable pageable);


    /**
     * 根据id批量删除
     * @param ids
     */
    @Modifying
    @Query("DELETE FROM tb_tc tc WHERE tc.id IN :ids")
    void deleteByIds(List<Integer> ids);


}
